Release 10.1A: OpenEdge Data Management:
SQL Development

Using inner joins

An inner join produces a results table consisting of only those rows that correspond to the tables specified in the query. A query expression can specify inner joins in either its FROM clause or its WHERE clause.

The syntax for an inner join is:

Syntax

{ FROM table_ref CROSS JOIN table_ref | FROM table_ref [ INNER ] JOIN table_ref ON search_condition }

FROM table_ref CROSS JOIN table_ref

This syntax is equivalent to omitting the WHERE clause and a search condition.

FROM table_ref [ INNER ] JOIN table_ref ON search_condition
FROM table_ref, table_ref WHERE search_condition

Specify search_condition for restricting rows that will be in the result table generated by the join. In the first format, INNER is optional and has no effect. There is no difference between the WHERE form of inner joins and the JOIN ON form.

This is the syntax for a where_clause_inner_join:

Syntax

FROM table_ref, table_ref WHERE search_condition

Queries in Example 6–10 illustrate the results of a simple CROSS JOIN operation and an equivalent formulation that does not use the CROSS JOIN syntax.

Example 6–10: Cross join statement

SELECT * FROM T1; -- Contents of T1 C1 C2 -- -- 10 15 20 25 SELECT * FROM T2; -- Contents of T2 C3 C4 -- -- 10 BB 15 DD SELECT * FROM T1 CROSS JOIN T2; C1 C2 C3 C4 -- -- -- --      10 15 15 DD 20 25 10 BB SELECT * FROM T1, T2; -- Different formulation, same results C1 C2 C3 C4 -- -- -- --      10 15 15 DD 20 25 10 BB

The following join is used to retrieve customer and customer order data:

SELECT Customer.Custnum, Customer.Name, Order.Ordernum, Order.Orderdate FROM Customer, Order WHERE Customer.Custnum = Order.num;

The statement produces the results table shown in Example 6–11.

Example 6–11: Cross join with results

[1] CustNum: 53 [1] Name: Offside Hockey [1] Ordernum: 1 [1] OrderDate: 2003-01-26 [2] CustNum: 81 [2] Name: Off The Wall [2] Ordernum: 2 [2] OrderDate: 2003-10-05